﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.IO;
using System.Collections;
using System.Threading;

public partial class Manage_Service_importstu : ManagePageBase
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //Thread.Sleep(10 * 1000);
        string dept = Request.Form["dept"].Trim();
        string filename = Request.Form["filename"].Trim();

        string numberList = string.Empty;//保存系统已经存在学号

        if (!IsPostBack) {
            //读取EXCEL表获取数据
            string path = Server.MapPath("~/upload/"+filename);
            DataTable dt = ExcelHelper.getFridtTable(path);
            ArrayList SQLList = new ArrayList();
            foreach (DataRow dr in dt.Rows) {
                //检索学号必须唯一
                string studentNumber = dr["学号"].ToString().Trim();
                if (!ExitsNumber(studentNumber.Trim())) {
                    string StudentName = dr["名字"].ToString().Trim();
                    string StudentPassword = dr["密码"].ToString().Trim();
                    string StudentEmail = dr["邮箱"].ToString().Trim();
                    string DepartmentID = dept;

                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("INSERT INTO Student(");
                    strSql.Append("StudentNumber,StudentName,StudentPassword,StudentEmail,DepartmentID)");
                    strSql.Append(" VALUES (");
                    strSql.Append("'" + studentNumber + "','" + StudentName + "','" + StudentPassword + "','" + StudentEmail + "'," + DepartmentID + ")");
                    int row = Exam.Core.DAL.DataProvider.ExecuteSql(strSql.ToString());
                    //SQLList.Add(strSql.ToString());
                }
                else {
                    numberList += studentNumber + ",";
                }
   
            }
            string result = "";
            try {
                //提交数据
                //Exam.Core.DAL.DataProvider.ExecuteSqlTranByArrayList(SQLList);
                result=numberList.Trim(',');
            }
            catch {
                result = "erro";
            }
            File.Delete(path);
            Response.Write(result);
            Response.End();

        }
    }

    private bool ExitsNumber(string p) {
        bool result = true;
        string SQL = "SELECT COUNT(*) FROM Student WHERE StudentNumber=@StudentNumber";
        OleDbParameter[] parameters = {
					new OleDbParameter("@StudentNumber", OleDbType.VarChar,100)};
        parameters[0].Value = p;
        object o = Exam.Core.DAL.DataProvider.GetSingle(SQL, parameters);
        if (null != o) {
            if (o.ToString().Trim() == "0") {//不存在 返回false
                result = false;
            }
        }
        return result;
    }
}